﻿/*数据库链接池*/
using System.Collections.Concurrent;
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;

namespace seejee_Backendium.Data.Services
{
    public interface IConnectionPoolService
    {
        Task<SqlConnection> GetConnectionAsync(string connectionString);
        Task ReturnConnectionAsync(string connectionString, SqlConnection connection);
        Task<bool> TestConnectionAsync(string connectionString);
    }

    public class ConnectionPoolService : IConnectionPoolService, IDisposable
    {
        private readonly ConcurrentDictionary<string, ConnectionPool> _pools;
        private readonly IMemoryCache _memoryCache;
        private readonly ILogger<ConnectionPoolService> _logger;

        public ConnectionPoolService(IMemoryCache memoryCache, ILogger<ConnectionPoolService> logger)
        {
            _pools = new ConcurrentDictionary<string, ConnectionPool>();
            _memoryCache = memoryCache;
            _logger = logger;
        }

        public async Task<SqlConnection> GetConnectionAsync(string connectionString)
        {
            var pool = _pools.GetOrAdd(connectionString, key => new ConnectionPool(key));
            return await pool.GetConnectionAsync();
        }

        public async Task ReturnConnectionAsync(string connectionString, SqlConnection connection)
        {
            if (_pools.TryGetValue(connectionString, out var pool))
            {
                await pool.ReturnConnectionAsync(connection);
            }
            else
            {
                // 如果池不存在，直接关闭连接
                await connection.CloseAsync();
                connection.Dispose();
            }
        }

        public async Task<bool> TestConnectionAsync(string connectionString)
        {
            try
            {
                using var connection = new SqlConnection(connectionString);
                await connection.OpenAsync();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void Dispose()
        {
            foreach (var pool in _pools.Values)
            {
                pool.Dispose();
            }
            _pools.Clear();
        }

        private class ConnectionPool : IDisposable
        {
            private readonly string _connectionString;
            private readonly ConcurrentBag<SqlConnection> _availableConnections;
            private readonly SemaphoreSlim _semaphore;
            private const int MaxPoolSize = 10;

            public ConnectionPool(string connectionString)
            {
                _connectionString = connectionString;
                _availableConnections = new ConcurrentBag<SqlConnection>();
                _semaphore = new SemaphoreSlim(MaxPoolSize);
            }

            public async Task<SqlConnection> GetConnectionAsync()
            {
                await _semaphore.WaitAsync();

                if (_availableConnections.TryTake(out var connection))
                {
                    if (await IsConnectionValidAsync(connection))
                        return connection;

                    connection.Dispose();
                }

                // 创建新连接
                connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();
                return connection;
            }

            public async Task ReturnConnectionAsync(SqlConnection connection)
            {
                if (connection.State == ConnectionState.Open && _availableConnections.Count < MaxPoolSize)
                {
                    _availableConnections.Add(connection);
                }
                else
                {
                    await connection.CloseAsync();
                    connection.Dispose();
                }

                _semaphore.Release();
            }

            private async Task<bool> IsConnectionValidAsync(SqlConnection connection)
            {
                try
                {
                    if (connection.State != ConnectionState.Open)
                        return false;

                    using var command = new SqlCommand("SELECT 1", connection);
                    var result = await command.ExecuteScalarAsync();
                    return result?.ToString() == "1";
                }
                catch
                {
                    return false;
                }
            }

            public void Dispose()
            {
                foreach (var connection in _availableConnections)
                {
                    connection.Dispose();
                }
                _availableConnections.Clear();
                _semaphore.Dispose();
            }
        }
    }
}